import toMysql
from datetime import date, datetime, timedelta
import weixin
from apscheduler.schedulers.blocking import BlockingScheduler

def mysql_select(cursor, tablename, line, point, interval=120):
    daytime = datetime.now() - timedelta(minutes=int(interval))
    day = daytime.date().__format__('%Y-%m-%d')
    time = daytime.time().__format__('%H:%M:%S')
    sql = "SELECT DISTINCT line,AlarmDate,AlarmTime,AlarmType,VarName FROM {0} WHERE AlarmDate ='{1}' AND AlarmTime >= '{2}' " \
          "AND VarName LIKE '{3}%' AND `line` LIKE '{4}%' ORDER BY AlarmDate, AlarmTime".format(tablename, day, time, point, line)
    # sql = "SELECT AlarmDate,AlarmTime,AlarmType,VarName FROM pause WHERE AlarmDate='{0}' AND AlarmTime='{1}'AND AlarmType='{2}'AND VarName='{3}'".format(set[0],set[1],set[2],set[3])
    cursor.execute(sql)
    data = cursor.fetchall()
    return data

def get_locals(cursor, tablename):
    '''
    :param cur: 数据库指针
    :param tablename: 数据库的表单名称。
    :return: 返回一个二维数组，包含所有的VarName（组态王的变量名称）
    '''
    day = datetime.today().__format__('%Y-%m-%d')
    sql = "SELECT DISTINCT VarName FROM {0} WHERE AlarmDate = '{1}'".format(tablename, day)
    cursor.execute(sql)
    data = cursor.fetchall()
    return data


def auto_send(line, interval):
    conn, cursor = toMysql.mysql_connect()
    # 表的名称
    tablename = 'pause'
    # 获得字段名字
    points = get_locals(cursor, tablename)
    result_num = {}
    result_time = {}
    for point in points:
        # 获得数据dict格式
        data = mysql_select(cursor, tablename, 'w7', point['VarName'].strip(),interval)
        num = 0
        total_time = 0
        # 求按下的次数
        for d in data:
            if '按下' in d['AlarmType']:
                num += 1
        # 求每个点松开时间与按下时间的差值，求和
        for index in range(len(data) - 1, 0, -1):
            if '松开' in data[index]['AlarmType'] or '恢复' in data[index]['AlarmType']:
                # 松开的时间
                close_time = data[index]['AlarmTime']
                # print('松开的时间是：',point,close_time)
                # 设定步长
                sep = 1
                while True:
                    if '按下' in data[index - sep]['AlarmType']:
                        # 按下的时间
                        opentime = data[index - sep]['AlarmTime']
                        # print('按下的时间是：',point,opentime)
                        break
                time = (close_time - opentime).seconds
                if time > 300:
                    time = 0
                total_time = time + total_time
        result_num.setdefault(point['VarName'].strip(), num)
        result_time.setdefault(point['VarName'].strip(), total_time)
    top3 = []
    lasttime = datetime.now()
    starttime = lasttime - timedelta(minutes=int(interval))
    for k in sorted(result_time, key=result_time.__getitem__, reverse=True):
        top3.append([k, result_time[k]])
    if top3[0][1] < 60:
        text = '{0}-{1}，{2}线急停时间均少于60秒'.format(starttime.strftime('%H:%M'), lasttime.strftime('%H:%M'), line)
    else:
        text = '{0}-{1}，{2}线急停次数前三位是：\n{3}：\n按下{4}次，影响时间合计{5}秒\n{6}：\n' \
               '按下{7}次，影响时间合计{8}秒\n{9}：\n按下{10}次，影响时间合计{11}秒' \
            .format(starttime.strftime('%H:%M'), lasttime.strftime('%H:%M'), line,
                    top3[0][0], result_num[top3[0][0]], top3[0][1],
                    top3[1][0], result_num[top3[1][0]], top3[1][1],
                    top3[2][0], result_num[top3[2][0]], top3[2][1])
    weixin.sendweixin(text)


if __name__ == '__main__':
    # 连接MySQL数据库
    interval = 20
    line = 'w7'
    #auto_send(line,interval)
    scheduler = BlockingScheduler()
    scheduler.add_job(auto_send, 'cron', minute ='0,10,20,30,40,50', args=[line, interval])
    scheduler.start()

